    /*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package org.dusin.teachassistdbservice.serviceimpl;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.dusin.teachassist.entity.MyConstant;
import org.dusin.teachassistdbservice.dao.AttendanceDao;
import org.dusin.teachassistdbservice.dao.HomeworkDao;
import org.dusin.teachassistdbservice.dao.JobDao;
import org.dusin.teachassistdbservice.dao.StudentDao;
import org.dusin.teachassistdbservice.dao.TeachClassDao;
import org.dusin.teachassistdbservice.dao.TeacherDao;

import org.dusin.teachassistdbservice.util.DateUtil;
import org.dusin.teachassistdbservice.util.TheUtil;
import org.springframework.dao.DuplicateKeyException;

/**
 *
 * @author dusin
 */
public class GeneralService {

    private static final String regExp = "^\\d{9,15}$";
    private static final Pattern p = Pattern.compile(regExp);
    static Logger logger = Logger.getLogger(GeneralService.class);
    JobDao jobDao;
    HomeworkDao homeworkDao;
    AttendanceDao attendanceDao;
    StudentDao studentDao;
    TeachClassDao teachClassDao;
    TeacherDao teacherDao;

    public GeneralService(JobDao jobDao, HomeworkDao homeworkDao, AttendanceDao attendanceDao, StudentDao studentDao,
            TeachClassDao teachClassDao, TeacherDao teacherDao) {
        this.jobDao = jobDao;
        this.homeworkDao = homeworkDao;
        this.attendanceDao = attendanceDao;

        this.studentDao = studentDao;
        this.teachClassDao = teachClassDao;
        this.teacherDao = teacherDao;
    }

    //从平时记录表Excel文件中创建相关记录
    public Map<String, Object> createFromRoster(InputStream stream) {
        Map<String, Object> result = new HashMap<String, Object>();
        StringBuilder sb = new StringBuilder();
        sb.append("");
        boolean hasNextRow = true;
        int nullRowCount = 0;
        boolean hasMoreCol = true;
        int nullColCount = 0; //连续扫描到的空列数码
        long teachClassID = -1;
        Workbook wb;
        try {
            int r = 0;  //目前遍历的行号
            int c = 0;  //目前遍历的列号
            int noCol = -1;//序号所在的列
            int xuehaoCol = -1;//学号所在的列
            int nameCol = -1;//姓名所在的列
            String[] teachclass = new String[6];//教学班数据
            List<Object[]> allStudents = new ArrayList<Object[]>();
            List<Object[]> allSC = new ArrayList<Object[]>();//学生选课
            List<Object[]> studentResults = new ArrayList<Object[]>(); //学生信息数据库操作结果，是否成功
            List<Object[]> scResults = new ArrayList<Object[]>(); //选课数据库操作结果，是否成功
            wb = new HSSFWorkbook(stream);
            Sheet sheet = wb.getSheetAt(0);
            boolean studentListStarted = false;//开始遍历到姓名列表
            while (hasNextRow) { //遍历每一行
                Row row = sheet.getRow(r++);
                if (row == null) {
                    nullRowCount++;
                    if (nullRowCount < 8) {
                        continue;
                    } else {
                        hasMoreCol = false;
                        break;
                    }
                }
                c = 0;
                hasMoreCol = true;
                nullColCount = 0;
                if (!studentListStarted) {
                    while (hasMoreCol) {//处理每一列
                        Cell cell = row.getCell(c, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                        String value = getStringValue(cell);
                        if (value != null) {
                            if (value.startsWith("教学班号")) {
                                teachclass[0] = value.split("[：:\\s]")[1];
                            }
                            if (value.startsWith("课程名称")) {
                                teachclass[1] = value.split("[：:]")[1];
                            }
                            if (value.startsWith("开课学期")) {
                                teachclass[2] = value.split("[：:\\s]")[1];
                            }
                            if (value.startsWith("学分")) {
                                teachclass[3] = value.split("[：:\\s]")[1];
                            }
                            if (value.startsWith("考核方式")) {
                                teachclass[4] = value.split("[：:\\s]")[1];
                            }
                            if (value.startsWith("任课教师")) {
                                teachclass[5] = value.split("[：:\\s]")[1];
                            }
                            if (value.trim().equals("序号")) {
                                noCol = c;
                                studentListStarted = true;
                            }
                            if (value.trim().equals("姓名")) {
                                nameCol = c;
                                studentListStarted = true;
                            }
                            if (value.trim().equals("学号")) {
                                xuehaoCol = c;
                                studentListStarted = true;
                            }
                        } else {
                            nullColCount++;
                            if (nullColCount > 20) {
                                hasMoreCol = false;
                            }
                        }
                        c++;
                    }
                } else {
                    Cell cell = row.getCell(noCol, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                    String no = getStringValue(cell);
                    cell = row.getCell(xuehaoCol, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                    String xuehao = getStringValue(cell);
                    cell = row.getCell(nameCol, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                    String name = getStringValue(cell);
                    if (isValid(no, xuehao, name)) { //如果是有效的数据
                        String pinyin = TheUtil.name2Pinyin(name);
                        allStudents.add(new Object[]{xuehao, name, pinyin, xuehao});
                        allSC.add(new Object[]{no, teachclass[0], xuehao});
                    }
                }
            }
            sb.append("扫描到课程信息:" + teachclass[0]).append(",").append(teachclass[1])
                    .append(teachclass[2]).append(teachclass[3]).append(teachclass[4])
                    .append(teachclass[5]).append("\n");
            sb.append("扫描到学生数据：" + allStudents.size());
            logger.info(sb.toString());
            //创建数据库数据
            String teacher[] = teachclass[5].split("[()（）]");
            logger.info("准备创建数据库数据");
            try {
                result.put("teacher", teacher[0] + "|" + teacher[1]);
                teacherDao.doCreate(new Object[]{teacher[1], teacher[0], teacher[1], 0});
                logger.info("教师数据已创建(" + teacher[0] + ")");
                result.put("teacherStatus", 1);  //创建成功
            } catch (DuplicateKeyException e) {
                result.put("teacherStatus", 0);  //已存在
                logger.info("教师数据已存在(" + teacher[0] + ")");
            } catch (Exception e) {
                result.put("teachStatus", -1);// 创建失败
                logger.info("教师数据创建失败(" + teacher[0] + ")");
            }

            try {
                result.put("class", teachclass[0] + "|" + teachclass[1] + "|"   //课程信息竖线分割
                        + teachclass[2] + "|" + teachclass[3] + "|" + teachclass[4]);
                teachClassID = teachClassDao.doCreate(new Object[]{teachclass[0], teachclass[1], teachclass[2], teachclass[3], teachclass[4], teacher[1]});
                result.put("classStatus", 1); //成功
                logger.info("教学班级创建成功(" + teachClassID + "," + teachclass[0] + ")");
            } catch (DuplicateKeyException e) {
                result.put("classStatus", 0); //已存在
                logger.info("教学班级已存在(" + teachclass[0] + ")");
                Map<String, Object> teachClass = teachClassDao.findByNo(teachclass[0], teachclass[2]);
                teachClassID = (Integer) teachClass.get("ID");
            } catch (Exception e) {
                result.put("classStatus", -1);//失败
                logger.info("教学班级创建失败(" + teachclass[0] + ")");
                teachClassID = -1;
                e.printStackTrace();
            }

            int i = 0;
            for (Object[] stu : allStudents) {
                try {
                    studentDao.doCreate(stu);
                    i++;
                    studentResults.add(new Object[]{stu[0], stu[1], 1});
                } catch (DuplicateKeyException e) {
                    logger.info("学生已存在(" + stu[0] + ")");
                    studentResults.add(new Object[]{stu[0], stu[1], 0});
                } catch (Exception e) {
                    logger.info("学生创建失败(" + stu[0] + ")" + e.getMessage());
                    studentResults.add(new Object[]{stu[0], stu[1], -1});
                }
            }
            logger.info("成功创建" + i + "/" + allStudents.size() + "条学生数据");
            result.put("student", studentResults);
            if (teachClassID == -1) {  //教学班级没有创建成功，则无法建立学生和课程间的选课关系
                return result;
            }
            i = 0;
            for (Object[] sc : allSC) {
                try {
                    studentDao.doCourse(new Object[]{sc[0], teachClassID, sc[2], ""});
                    i++;
                    scResults.add(new Object[]{sc[0], sc[1], 1});
                } catch (DuplicateKeyException e) {
                    logger.info("学生选课已存在(" + sc[0] + "," + sc[1] + ")");
                    scResults.add(new Object[]{sc[0], sc[1], 0});
                } catch (Exception e) {
                    logger.info("学生选课创建失败(" + sc[0] + "," + sc[1] + ")");
                    scResults.add(new Object[]{sc[0], sc[1], -1});
                }
            }
            result.put("sc", scResults);
            logger.info("成功创建" + i + "/" + allStudents.size() + "条学生选课");
        } catch (IOException ex) {
            sb.append(ex.getMessage());
        }

        return result;
    }

    private String getStringValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        CellType ct = cell.getCellType();
        if (CellType.STRING.equals(ct)) {
            return (cell.getRichStringCellValue().getString().trim());
        } else if (CellType.NUMERIC.equals(ct)) {
            if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue().toString();
            } else {
                return String.valueOf(cell.getNumericCellValue());
            }
        } else if (CellType.BOOLEAN.equals(ct)) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (CellType.FORMULA.equals(ct)) {
            return cell.getCellFormula().toString();
        } else {
            return null;
        }

    }

    private boolean isValid(String no, String xuehao, String name) {
        boolean result = false;
        try {
            int noInt = Integer.parseInt(no);
            if (noInt > 0 && noInt < 500) {
                result = true;
            } else {
                result = false;
            }
            Matcher m = p.matcher(xuehao);
            result = m.matches();
        } catch (Exception e) {
            //e.printStackTrace();
            result = false;
        }
        return result;
    }

    //创建一次实验任务
    public boolean trCreateLab(int teachClassId, int no, String title, String duedate, int threshold,String note) {

        return jobDao.doCreate(new Object[]{teachClassId, no, title, duedate, threshold,1, note});
    }

    //修改实验任务
    public boolean updateLab(int id, int no, String title, String duedate,int threshold, String note) {
        return jobDao.doUpdate(new Object[]{no, title, duedate,threshold, note, id});
    }

    //检索所有实验任务
    public List<Map<String, Object>> findAllLabs(int teachClassId) {
        return jobDao.findAllByTeachClassId(teachClassId, 1);
    }

    public boolean destroyLab(int id) {
        return jobDao.doDeleteByID(id);
    }

    //查找当前学期某个教师所有课程
    public List<Map<String, Object>> findClass(String teacherNo, String semester) {
        return teachClassDao.findAllClass(teacherNo, semester);
    }

    //查找当前学期某个学生的所有课程
    public List<Map<String, Object>> findClass4Student(String studentNo, String semester) {
        return teachClassDao.findAllClass4Student(studentNo, semester);
    }

    public boolean createStudent(String sn, String name, String password) {
        String py = TheUtil.name2Pinyin(name);
        return studentDao.doCreate(new Object[]{sn, name, py, password});
    }

    public boolean destroyStudent(String sn) {
        return studentDao.doDeleteByNo(sn);
    }

    public boolean updateStudent(String sn, String name) {
        String py = TheUtil.name2Pinyin(name);
        return studentDao.doUpdate(new Object[]{name, py, sn});
    }

    public boolean updateStudentPassword(String sn, String pwd) {
        return studentDao.doUpdatePassword(sn, pwd);
    }

    //创建选课关系,学号和课程号想关联
    public boolean createSC(int no, int classID, String studentno, String note) {
        return studentDao.doCourse(new Object[]{no, classID, studentno, note});
    }

    public boolean destroySC(String sn, int classID) {
        return studentDao.doDeleteCourse(sn, classID);
    }

    public boolean updateSCNote(String sn, int classId, String note) {
        return studentDao.doUpdateNote(sn, classId, note);
    }

    public List<Map<String, Object>> findAllStudents(int classId) {
        return studentDao.findAllByClassID(classId);
    }

    public Map<String,Object> findStudentByNo(String no){
        return studentDao.findByNo(no);
    }
    public List<Map<String, Object>> findAllCourseJobs4Student(int teachClassId, String StudentNo) {
        return jobDao.findAllCourseJobs4Student(teachClassId, StudentNo);
    }

    public boolean trUpdateScore(int jobId, String studentNo,int score, int status,
            String note,String filename,double degree,String matchFilename) {
        if (jobDao.isScoreExisted(jobId, studentNo)) {
            return jobDao.doUpdateReport(jobId, studentNo,score,status,note, filename,degree,matchFilename);
        } else {
            String time = DateUtil.get(new Date());
            return jobDao.doSubmitReport(new Object[]{jobId, studentNo, 0, time
                    , 1, status, filename, note,degree,matchFilename});
        }
    }
    
    public boolean trUpdateHomework(int jobId, String studentNo, String filename) {
        if (homeworkDao.isScoreExisted(jobId, studentNo)) {
            return homeworkDao.doUpdateReport(jobId, studentNo, filename);
        } else {
            String time = DateUtil.get(new Date());  //提交报告后分数置0,表示报告一提交
            return homeworkDao.doSubmitReport(new Object[]{jobId, studentNo,0, time, 1, 1, filename, ""});
        }
    }

    public boolean trCancelReport(int jobId, String studentNo) {
        return jobDao.doCancelReport(jobId, studentNo);
    }

    public boolean trCancelHomework(int jobId, String studentNo) {
        return homeworkDao.doCancelReport(jobId, studentNo);
    }

    public Map<String, Object> login(String user, String password) {
        return studentDao.login(user, password);
    }

    public Map<String, Object> loginTeacher(String user, String password) {
        return teacherDao.login(user, password);
    }

    public List<Map<String, Object>> findAllClasses4Teacher(String semester, String teacherNo) {
        return teachClassDao.findAllClass(teacherNo, semester);
    }

    //创建一次新考勤
    public long trCreateAttendance(int teachClassId, int no, String note, List<Object[]> beans) {
        boolean flag = true;
        String nowStr = DateUtil.get();
        long id = attendanceDao.doCreateWithGetId(new Object[]{teachClassId, no, note, nowStr});

        List<Object[]> bs = new ArrayList<Object[]>(beans.size());
        for (Object[] o : beans) {
            Object[] b = new Object[5];
            b[0] = id;
            b[1] = o[0];
            b[2] = o[1];
            b[3] = nowStr;
            b[4] = o[2];
            bs.add(b);
        }
        if (bs.size() > 0) {
            attendanceDao.doCreateAttendance(bs);
        }
        return id;
    }

    //更新考勤
    public boolean trUpdateAttendance(int attendanceId, List<Object[]> beans) {
        boolean flag = true;
        String nowStr = DateUtil.get();
        List<Object[]> bs = new ArrayList<Object[]>();
        List<Object[]> recToBeDeleted = new ArrayList<Object[]>();
        for (Object[] o : beans) {
            Integer status = Integer.parseInt((String) o[1]);
            Object[] os1 = new Object[2];
            os1[0] = new Integer(attendanceId);
            os1[1] = o[0];
            recToBeDeleted.add(os1);
            if (status != 0) {
                Object[] b = new Object[5];
                b[0] = attendanceId;
                b[1] = o[0];
                b[2] = status;
                b[3] = nowStr;
                b[4] = o[2];
                bs.add(b);
            }
        }
        if (recToBeDeleted.size() > 0) {
            attendanceDao.doDeleteDetail(recToBeDeleted);
        }
        if (bs.size() > 0) {
            attendanceDao.doCreateAttendance(bs);
        }
        return flag;
    }

    public boolean deleteAttendances(int attendanceId) {
        return attendanceDao.doDeleteByID(attendanceId);
    }

    //检索所有考勤
    public List<Map<String, Object>> findAllAttendances(int teachClassId) {
        return attendanceDao.findAllByTeachClassId(teachClassId);
    }

    //检索所有考勤明细
    public List<Map<String, Object>> findAllAttendanceDetail(int teachClassId, int attendanceId) {
        return studentDao.findAttendanceDetail(teachClassId, attendanceId);
    }

    //检索考勤表
    public List<Map<String, Object>> findAllAttendanceTable(int teachClassId) {
        List<Map<String, Object>> stuList = studentDao.findAllByClassID(teachClassId);
        List<Map<String, Object>> attendanceList = attendanceDao.findAllByTeachClassId(teachClassId);
        List<Map<String, Object>> detailList = attendanceDao.findAllAttendanceDetail(teachClassId);
        Map<String, List<Object[]>> mapDetail = new HashMap<String, List<Object[]>>();
        Map<Integer, Integer> attendanceId2No = new HashMap<Integer, Integer>();//attendanceId -> no
        for (Map<String, Object> a : attendanceList) {
            Integer aId = (Integer) a.get("id");
            Integer no = (Integer) a.get("no");
            attendanceId2No.put(aId, no);
        }

        for (Map<String, Object> d : detailList) {
            String studentNo = (String) d.get("studentno");
            Integer aId = (Integer) d.get("attendanceId");
            Integer status = (Integer) d.get("status");
            Integer no = attendanceId2No.get(aId);
            List<Object[]> alist = mapDetail.get(studentNo);
            if (alist == null) {
                alist = new ArrayList<Object[]>();
                alist.add(new Object[]{no, status});
                mapDetail.put(studentNo, alist);
            } else {
                alist.add(new Object[]{no, status});
            }

        }
        for (Map<String, Object> s : stuList) {
            String studentNo = (String) s.get("studentno");
            List<Object[]> details = mapDetail.get(studentNo);
            if (details != null) {
                for (Object[] os : details) {
                    s.put("no" + os[0], os[1]);
                }
            }
        }
        return stuList;
    }

    public long createTeachClass(String teachclassno, String coursename, String coursesemester, int credit,
            String evalmethod, String teacherno) {
        return teachClassDao.doCreate(new Object[]{teachclassno, coursename, coursesemester, credit + "", evalmethod, teacherno});

    }

    public boolean updateCourse(int id, String teachclassno, String courseName, String semester, Integer credit, String evalmethod) {
        return teachClassDao.doUpdate(new Object[]{teachclassno, courseName, semester, credit, evalmethod, id});
    }

    public boolean destroyCourse(int id) {
        return teachClassDao.doDeleteById(id);
    }

    public List<Map<String, Object>> findAllHomeworks(int teachClassId) {
        //检索所有实验任务
        return homeworkDao.findAllByTeachClassId(teachClassId, 2);
    }

    //创建一次实验任务,并且创建每一位同学的提交作业记录（homeworkdetail表）
    public boolean trCreateHomework(int teachClassId, int no, String title, String duedate, String note) {

        long homeworkId = homeworkDao.doCreateWithGetId(new Object[]{teachClassId, no, title, duedate, 2, note});
        //查询所有选项同学学号
        List<Map<String, Object>> stus = studentDao.findAllByClassID(teachClassId);
        List<Object[]> homeworkDetails = new ArrayList<Object[]>();
        for (Map<String, Object> map : stus) {
            String sno = (String) map.get("studentno");
            Object[] b = {homeworkId, sno, -1, "1970-01-01", 0, 0, "", ""};
            homeworkDetails.add(b);
        }
        homeworkDao.doCreateDetailBatch(homeworkDetails); 
        //创建每一位同学的成绩记录
        return true;
    }

    //修改实验任务
    public boolean updateHomework(int id, int no, String title, String duedate, String note) {
        return homeworkDao.doUpdate(new Object[]{no, title, duedate, note, id});
    }

    public boolean destroyHomework(int id) {

        return homeworkDao.doDeleteByID(id);
    }

    //更新作业分数
    public boolean trUpdateHomeworkDetail(int homeworkId, List<Object[]> beans) {
        boolean flag = true;

        List<Object[]> recToBeUpdated = new ArrayList<Object[]>();
        List<Object[]> recToBeCreated = new ArrayList<Object[]>();
        for (Object[] o : beans) {
            String sno = (String) o[0];
            Integer score = Integer.parseInt((String) o[1]);
            String note = (String) o[2];
            Object[] wd1 = new Object[]{score, note, homeworkId, sno};
            recToBeUpdated.add(wd1);
        }
        int[] rows = null;
        if (recToBeUpdated.size() > 0) {
            rows = homeworkDao.doUpdateScoreBatch(recToBeUpdated);
        }

        if (rows != null && rows.length > 0) {
            int i = 0;
            for (int r : rows) {
                if (r == 0) {  //表明该记录不存在，所以需要创建
                    Object[] b = recToBeUpdated.get(i);
                    String sno = (String) b[3];
                    Integer score = (Integer) b[0];
                    String note = (String) b[1];
                    recToBeCreated.add(new Object[]{homeworkId, sno, score, "1970-01-01", 0, 0, "", note});
                }
                i++;
            }
            homeworkDao.doCreateDetailBatch(recToBeCreated);
        }
        return flag;
    }

    public List<Map<String, Object>> findAllHomeworkDetail(int teachClassId, int attendanceId) {
        return homeworkDao.findHomeworkDetail(teachClassId, attendanceId);
    }

    public List<Map<String, Object>> findAllHomeworks4Student(int teachClassId, String studentNo) {
        return homeworkDao.findAllCourseJobs4Student(teachClassId, studentNo);
    }

    public List<Map<String, Object>> findAllAttendances4Student(int teachClassId, String studentNo) {
        return attendanceDao.findAllAttendance4Student(teachClassId, studentNo);
    }

    public List<Map<String, Object>> findAllHomeworkTable(int teachClassId) {
        List<Map<String, Object>> stuList = studentDao.findAllByClassID(teachClassId);
        List<Map<String, Object>> homeworkList = homeworkDao.findAllByTeachClassId(teachClassId);
        List<Map<String, Object>> detailList = homeworkDao.findAllHomeworkDetail(teachClassId);
        Map<String, List<Object[]>> mapDetail = new HashMap<String, List<Object[]>>();
        Map<Integer, Integer> homeworkId2No = new HashMap<Integer, Integer>();//homeworkId -> no
        for (Map<String, Object> a : homeworkList) {
            Integer aId = (Integer) a.get("id");
            Integer no = (Integer) a.get("no");
            homeworkId2No.put(aId, no);
        }

        for (Map<String, Object> d : detailList) {
            String studentNo = (String) d.get("studentno");
            Integer aId = (Integer) d.get("jobId");
            Integer score = (Integer) d.get("score");
            Integer no = homeworkId2No.get(aId);
            List<Object[]> alist = mapDetail.get(studentNo);
            if (alist == null) {
                alist = new ArrayList<Object[]>();
                alist.add(new Object[]{no, score});
                mapDetail.put(studentNo, alist);
            } else {
                alist.add(new Object[]{no, score});
            }

        }
        for (Map<String, Object> s : stuList) {
            String studentNo = (String) s.get("studentno");
            List<Object[]> details = mapDetail.get(studentNo);
            if (details != null) {
                for (Object[] os : details) {
                    s.put("no" + os[0], os[1]);
                }
            }
        }
        return stuList;
    }

    //将作业序号生成 json数组,如[1,2,3,4]等
    public String findHomeworkNos(int teachclassId) {
        List<Map<String, Object>> list = homeworkDao.findAllByTeachClassId(teachclassId);
        StringBuilder sb = new StringBuilder();
        sb.append('[');
        if (list != null) {
            for (Map<String, Object> map : list) {
                Integer no = (Integer) map.get("no");
                sb.append(no);
                sb.append(',');
            }
        }
        if (sb.charAt(sb.length() - 1) == ',') {
            sb.deleteCharAt(sb.length() - 1);
        }
        sb.append(']');
        return sb.toString();
    }

    //将作业序号生成 json数组,如[1,2,3,4]等
    public String findLabNos(int teachclassId) {
        List<Map<String, Object>> list = jobDao.findAllByTeachClassId(teachclassId);
        StringBuilder sb = new StringBuilder();
        sb.append('[');
        if (list != null) {
            for (Map<String, Object> map : list) {
                Integer no = (Integer) map.get("no");
                sb.append(no);
                sb.append(',');
            }
        }
        if (sb.charAt(sb.length() - 1) == ',') {
            sb.deleteCharAt(sb.length() - 1);
        }
        sb.append(']');
        return sb.toString();
    }

    public List<Map<String, Object>> findAllLabDetail(int teachClassId, int attendanceId) {
        return jobDao.findHomeworkDetail(teachClassId, attendanceId);
    }

    public boolean trUpdateLabDetail(int homeworkId, List<Object[]> beans) {
        boolean flag = true;

        List<Object[]> recToBeUpdated = new ArrayList<Object[]>();
        List<Object[]> recToBeCreated = new ArrayList<Object[]>();
        for (Object[] o : beans) {
            String sno = (String) o[0];
            Integer score = Integer.parseInt((String) o[1]);
            String note = (String) o[2];
            Object[] wd1 = new Object[]{score, note, homeworkId, sno};
            recToBeUpdated.add(wd1);
        }
        int[] rows = null;
        if (recToBeUpdated.size() > 0) {
            rows = jobDao.doUpdateScoreBatch(recToBeUpdated);
        }

        if (rows != null && rows.length > 0) {
            int i = 0;
            for (int r : rows) {
                if (r == 0) {  //表明该记录不存在，所以需要创建
                    Object[] b = recToBeUpdated.get(i);
                    String sno = (String) b[3];
                    Integer score = (Integer) b[0];
                    String note = (String) b[1];
                    recToBeCreated.add(new Object[]{homeworkId, sno, score, "1970-01-01", 0, 0, "", note});
                }
                i++;
            }
            jobDao.doCreateDetailBatch(recToBeCreated);
        }
        return flag;
    }

    public List<Map<String, Object>> findAllLabTable(int teachClassId) {
        List<Map<String, Object>> stuList = studentDao.findAllByClassID(teachClassId);
        List<Map<String, Object>> homeworkList = jobDao.findAllByTeachClassId(teachClassId);
        List<Map<String, Object>> detailList = jobDao.findAllJobDetail(teachClassId);
        Map<String, List<Object[]>> mapDetail = new HashMap<String, List<Object[]>>();
        Map<Integer, Integer> homeworkId2No = new HashMap<Integer, Integer>();//homeworkId -> no
        for (Map<String, Object> a : homeworkList) {
            Integer aId = (Integer) a.get("id");
            Integer no = (Integer) a.get("no");
            homeworkId2No.put(aId, no);
        }

        for (Map<String, Object> d : detailList) {
            String studentNo = (String) d.get("studentno");
            Integer aId = (Integer) d.get("jobId");
            Integer score = (Integer) d.get("score");
            Integer no = homeworkId2No.get(aId);
            List<Object[]> alist = mapDetail.get(studentNo);
            if (alist == null) {
                alist = new ArrayList<Object[]>();
                alist.add(new Object[]{no, score});
                mapDetail.put(studentNo, alist);
            } else {
                alist.add(new Object[]{no, score});
            }

        }
        for (Map<String, Object> s : stuList) {
            String studentNo = (String) s.get("studentno");
            List<Object[]> details = mapDetail.get(studentNo);
            if (details != null) {
                for (Object[] os : details) {
                    s.put("no" + os[0], os[1]);
                }
            }
        }
        return stuList;
    }

    public void trCreateStudent4Class(String sn, String name, int no, int teachclassid, String note) {
        //查找该学生是否存在
        Map<String,Object> s = studentDao.findByNo(sn);
        if(s==null){  //不存在，先创建学生
            boolean fStudent = this.createStudent(sn, name, sn); //默认密码为学号            
        }
        //创建选课关系
        this.createSC(no, teachclassid, sn, note);
    }

    public boolean lockLab(int id) {
        jobDao.doChangeStatus(id, MyConstant.JOB_LOCKED);
        return true;
    }
    
    public boolean publishLab(int id) {
        jobDao.doChangeStatus(id, MyConstant.JOB_PUBLISHED);
        return true;
    }

    public boolean lockHomework(int id) {
        homeworkDao.doChangeStatus(id, MyConstant.JOB_LOCKED);
        return true;
    }

    public boolean publishHomework(int id) {
        homeworkDao.doChangeStatus(id, MyConstant.JOB_PUBLISHED);
        return true;
    }
}
